package experiment5.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author:fyl
 * @date 2021/5/21 17:24
 * @Modified By:
 * @Modified Date:
 * @Description:
 */
public class JDBCTools<T> {
    /**
     * 增、删、改
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    public int insertOrDeleteOrUpdateBean(Connection connection,String sql,Object... params){
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            //填充sql参数
            fillParams(statement,params);
            return statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
         C3P0Tools.release(connection,statement,null);
        }
        return 0;
    }

    /**
     * 可以传sql参数返回数据集合
     * @param connection
     * @param sql
     * @param clazz
     * @param params
     * @return
     */
    public List<T> getBeans(Connection connection, String sql, Class clazz, Object... params) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();
        try {
            //查询sql
            statement = connection.prepareStatement(sql);
            //替换参数
            fillParams(statement, params);
            //获取结果集
            resultSet = statement.executeQuery();
            //解析结果集
            parseDataForList(resultSet, clazz, list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            C3P0Tools.release(connection, statement, resultSet);
        }
        return list;
    }

    /**
     * 查询对象集合
     * @param connection
     * @param sql
     * @param clazz
     * @return
     */
    public List<T> getBeans(Connection connection,String sql,Class clazz){
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();
        try {
            //查询sql
            statement = connection.prepareStatement(sql);
            //获取结果集
            resultSet = statement.executeQuery();
            //解析结果集
            parseDataForList(resultSet, clazz, list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            C3P0Tools.release(connection, statement, resultSet);
        }
        return list;
    }

    public T getBean(Connection connection,String sql,Class clazz,Object... params){
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //查询sql
            statement = connection.prepareStatement(sql);
            //替换参数
            fillParams(statement, params);
            //获取结果集
            resultSet = statement.executeQuery();
            //解析结果集
            Object instance = clazz.getConstructor().newInstance();
            parseDataForBean(resultSet, clazz,instance);
            return (T)instance;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            C3P0Tools.release(connection, statement, resultSet);
        }
        return null;
    }

    /**
     * 通过传sql参数查询单个对象
     * @param resultSet
     * @param clazz
     * @param object
     */
    private void parseDataForBean(ResultSet resultSet,Class clazz,Object object){
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {

                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String columnTypeName = metaData.getColumnTypeName(i);
                    Object value = null;
                    switch (columnTypeName) {
                        case "INT":
                            value =  resultSet.getInt(columnName);
                            break;
                        case "VARCHAR":
                            value = resultSet.getString(columnName);
                            break;
                        case "DATETIME":
                            value = resultSet.getTime(columnName);
                            break;
                    }
                    Field field = clazz.getDeclaredField(columnName);
                    String methodName = "set" + columnName.substring(0, 1).toUpperCase() + columnName.substring(1);
                    Method method = clazz.getMethod(methodName, field.getType());
                    method.invoke(object, value);
                }
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } catch (IllegalAccessException illegalAccessException) {
            illegalAccessException.printStackTrace();
        } catch (InvocationTargetException invocationTargetException) {
            invocationTargetException.printStackTrace();
        } catch (NoSuchMethodException noSuchMethodException) {
            noSuchMethodException.printStackTrace();
        } catch (NoSuchFieldException noSuchFieldException) {
            noSuchFieldException.printStackTrace();
        }
    }

    /**
     * 填充sql参数
     * @param statement
     * @param params
     */
    private void fillParams(PreparedStatement statement, Object... params) {
        try {
            for (int i = 0; i < params.length; i++) {
                if (params[i]==null){
                    continue;
                }
                String typeName = params[i].getClass().getTypeName();
                switch (typeName) {
                    case "java.lang.Integer":
                        statement.setInt(i + 1, (Integer) params[i]);
                        break;
                    case "java.lang.String":
                        statement.setString(i + 1, (String) params[i]);
                        break;
                    case "java.sql.Timestamp":
                        statement.setTimestamp(i+1, (Timestamp) params[i]);
                        break;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 解析结果集，返回数据集合
     * @param resultSet
     * @param clazz
     * @param list
     */
    private void parseDataForList(ResultSet resultSet, Class clazz, List<T> list) {
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                Object instance = clazz.getConstructor().newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String columnTypeName = metaData.getColumnTypeName(i);
                    Object value = null;
                    switch (columnTypeName) {
                        case "INT":
                            value = (Integer) resultSet.getInt(columnName);
                            break;
                        case "VARCHAR":
                            value = (String) resultSet.getString(columnName);
                            break;
                        case "DATETIME":
                            value = resultSet.getTimestamp(columnName);
                            break;
                    }
                    Field field = clazz.getDeclaredField(columnName);
                    String methodName = "set" + columnName.substring(0, 1).toUpperCase() + columnName.substring(1);
                    Method method = clazz.getMethod(methodName, field.getType());
                    method.invoke(instance, value);
                }
                list.add((T) instance);
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } catch (InstantiationException instantiationException) {
            instantiationException.printStackTrace();
        } catch (IllegalAccessException illegalAccessException) {
            illegalAccessException.printStackTrace();
        } catch (InvocationTargetException invocationTargetException) {
            invocationTargetException.printStackTrace();
        } catch (NoSuchMethodException noSuchMethodException) {
            noSuchMethodException.printStackTrace();
        } catch (NoSuchFieldException noSuchFieldException) {
            noSuchFieldException.printStackTrace();
        }
    }
}
